from typing import Optional
from fastapi import APIRouter, Depends, Query
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme

data_my_safe_02 = APIRouter(prefix="/phone", tags=["储物柜管理"], dependencies=[Depends(oauth2_scheme)])


class CabinetAbnormalSearch(PaginationBase):
    # 筛选条件
    fsiteNo: str = Field("", description="站点编号")
    fbegdate: str = Field("2000-01-01", description="开始日期(yyyy-MM-dd)，默认'2000-01-01'")
    fenddate: str = Field("2000-01-01", description="结束日期(yyyy-MM-dd)，默认'2000-01-01'")
    fpointNo: str = Field("", description="点位编号")
    boxFequipmentbarcode: str = Field("", description="箱设备条码")
    cabFequipmentbarcode: str = Field("", description="柜设备条码")


@data_my_safe_02.get("/getOperateAlarm", summary="储物柜-异常操作查询接口")
def list_my_safe_02_get(cabinet: CabinetAbnormalSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from ( select  to_char(a.fupddate,'yyyy-mm-dd hh24:mi:ss') fupddate "
    sql = sql + " ,b.username,t.fsite_no,t.fsite_name,s.fpoint_name,g.fequipmenttag fconname"
    sql = sql + " ,COALESCE(c.fequipmenttag, fcabbarcode) fcabname"
    sql = sql + " ,COALESCE(d.fequipmenttag, fboxbarcode) fboxname"
    sql = sql + " ,case faction when 0 then '取出' when 3 then '归还' when 1 then '入库' when 7 then '出库' when 8 then '盘点' end faction"
    sql = sql + " ,case fbiztype when 2 then '取' else '还' end fbiztype, frfidbarcode"
    sql = sql + " ,COALESCE(e.fgoodname, a.fgoodno) fgoodname,fmessage"
    sql = sql + " FROM i_warehouse_dtl a"
    sql = sql + " left join am_sys_user b on cast(b.id as varchar) = a.foperator "
    sql = sql + " left join i_ts_cabinet c on c.fequipmentbarcode = a.fcabbarcode "
    sql = sql + " left join lims.i_ts_warehouse d on d.fequipmentbarcode = a.fboxbarcode"
    sql = sql + " left join i_ts_good_info e on e.fgoodno = a.fgoodno"
    sql = sql + " left join i_ts_controlbox f on f.fcatbarcode=c.fequipmentbarcode"
    sql = sql + " left join i_ts_box g on g.fequipmentbarcode=f.fequipmentbarcode"
    sql = sql + " left join a_ts_point s on s.fpoint_no=c.fpoint_no"
    sql = sql + " left join a_ts_site t on s.fsite_no = t.fsite_no"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(a.fupddate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
    param["ftakedateEnd"] = cabinet.fbegdate
    param["ftakedateStart"] = cabinet.fenddate
    sql = sql + " and fwarringflag=1"
    if cabinet.fsiteNo != "":
        sql = sql + " and t.fsite_no= :fsiteNo "
        param["fsiteNo"] = cabinet.fsiteNo
    if cabinet.fpointNo != "":
        sql = sql + " and s.fpoint_no= :fpointNo "
        param["fpointNo"] = cabinet.fpointNo
    if cabinet.boxFequipmentbarcode != "":
        sql = sql + " and a.fboxbarcode= :boxFequipmentbarcode "
        param["boxFequipmentbarcode"] = cabinet.boxFequipmentbarcode
    if cabinet.cabFequipmentbarcode != "":
        sql = sql + " and a.fcabbarcode= :cabFequipmentbarcode "
        param["cabFequipmentbarcode"] = cabinet.cabFequipmentbarcode
    sql = sql + " UNION "
    sql = sql + " select to_char(h.fcheckdate,'yyyy-mm-dd hh24:mi:ss') fupddate,'' username,u.fsite_no,u.fsite_name,n.fpoint_name"
    sql = sql + " ,g.fequipmenttag fconname,i.fequipmenttag fcabname, j.fequipmenttag fboxname"
    sql = sql + " ,t.fdictname faction,t.fdictmemo fbiztype,k.frfidbarcode,l.fgoodname,h.fmessage"
    sql = sql + " from i_day_report h"
    sql = sql + " left join i_ts_cabinet i on i.fequipmentbarcode = h.fcabbarcode"
    sql = sql + " left join lims.i_ts_warehouse j on j.fequipmentbarcode = h.fboxbarcode"
    sql = sql + " left join i_warehouse_inv k on k.frfidbarcode = h.frfidbarcode and k.fstate=3"
    sql = sql + " left join i_ts_good_info l on k.fgoodno = l.fgoodno"
    sql = sql + " left join i_ts_controlbox m on m.fcatbarcode=i.fequipmentbarcode"
    sql = sql + " left join i_ts_box g on g.fequipmentbarcode=m.fequipmentbarcode"
    sql = sql + " left join a_ts_point n on i.fpoint_no = n.fpoint_no"
    sql = sql + " left join a_ts_site u on u.fsite_no = n.fsite_no"
    sql = sql + " left join s_ts_dict t on h.ftype=cast(t.fdictno as int) and t.fdicttype='3009'"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(h.fcheckdate,'yyyymmdd') between  :ftakedateStart  and :ftakedateEnd "
    sql = sql + " and h.ftype in ('101','104','105','107','108','109','110','111')"
    if cabinet.fsiteNo != "":
        sql = sql + " and h.fsite_no= :fsiteNo "
    if cabinet.fpointNo != "":
        sql = sql + " and i.fpoint_no= :fpointNo "
    if cabinet.boxFequipmentbarcode != "":
        sql = sql + " and h.fboxbarcode= :boxFequipmentbarcode "
    if cabinet.cabFequipmentbarcode != "":
        sql = sql + " and h.fcabbarcode= :cabFequipmentbarcode "
    sql = sql + " )"
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select  to_char(a.fupddate,'yyyy-mm-dd hh24:mi:ss') fupddate "
    sql = sql + " ,b.username,t.fsite_no,t.fsite_name,s.fpoint_name,g.fequipmenttag fconname"
    sql = sql + " ,COALESCE(c.fequipmenttag, fcabbarcode) fcabname"
    sql = sql + " ,COALESCE(d.fequipmenttag, fboxbarcode) fboxname"
    sql = sql + " ,case faction when 0 then '取出' when 3 then '归还' when 1 then '入库' when 7 then '出库' when 8 then '盘点' end faction"
    sql = sql + " ,case fbiztype when 2 then '取' else '还' end fbiztype, frfidbarcode"
    sql = sql + " ,COALESCE(e.fgoodname, a.fgoodno) fgoodname,fmessage"
    sql = sql + " FROM i_warehouse_dtl a"
    sql = sql + " left join am_sys_user b on cast(b.id as varchar) = a.foperator "
    sql = sql + " left join i_ts_cabinet c on c.fequipmentbarcode = a.fcabbarcode "
    sql = sql + " left join lims.i_ts_warehouse d on d.fequipmentbarcode = a.fboxbarcode"
    sql = sql + " left join i_ts_good_info e on e.fgoodno = a.fgoodno"
    sql = sql + " left join i_ts_controlbox f on f.fcatbarcode=c.fequipmentbarcode"
    sql = sql + " left join i_ts_box g on g.fequipmentbarcode=f.fequipmentbarcode"
    sql = sql + " left join a_ts_point s on s.fpoint_no=c.fpoint_no"
    sql = sql + " left join a_ts_site t on s.fsite_no = t.fsite_no"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(a.fupddate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
    param["ftakedateEnd"] = cabinet.fbegdate
    param["ftakedateStart"] = cabinet.fenddate
    sql = sql + " and fwarringflag=1"
    if cabinet.fsiteNo != "":
        sql = sql + " and t.fsite_no= :fsiteNo "
        param["fsiteNo"] = cabinet.fsiteNo
    if cabinet.fpointNo != "":
        sql = sql + " and s.fpoint_no= :fpointNo "
        param["fpointNo"] = cabinet.fpointNo
    if cabinet.boxFequipmentbarcode != "":
        sql = sql + " and a.fboxbarcode= :boxFequipmentbarcode "
        param["boxFequipmentbarcode"] = cabinet.boxFequipmentbarcode
    if cabinet.cabFequipmentbarcode != "":
        sql = sql + " and a.fcabbarcode= :cabFequipmentbarcode "
        param["cabFequipmentbarcode"] = cabinet.cabFequipmentbarcode
    sql = sql + " UNION "
    sql = sql + " select to_char(h.fcheckdate,'yyyy-mm-dd hh24:mi:ss') fupddate,'' username,u.fsite_no,u.fsite_name,n.fpoint_name"
    sql = sql + " ,g.fequipmenttag fconname,i.fequipmenttag fcabname, j.fequipmenttag fboxname"
    sql = sql + " ,t.fdictname faction,t.fdictmemo fbiztype,k.frfidbarcode,l.fgoodname,h.fmessage"
    sql = sql + " from i_day_report h"
    sql = sql + " left join i_ts_cabinet i on i.fequipmentbarcode = h.fcabbarcode"
    sql = sql + " left join lims.i_ts_warehouse j on j.fequipmentbarcode = h.fboxbarcode"
    sql = sql + " left join i_warehouse_inv k on k.frfidbarcode = h.frfidbarcode and k.fstate=3"
    sql = sql + " left join i_ts_good_info l on k.fgoodno = l.fgoodno"
    sql = sql + " left join i_ts_controlbox m on m.fcatbarcode=i.fequipmentbarcode"
    sql = sql + " left join i_ts_box g on g.fequipmentbarcode=m.fequipmentbarcode"
    sql = sql + " left join a_ts_point n on i.fpoint_no = n.fpoint_no"
    sql = sql + " left join a_ts_site u on u.fsite_no = n.fsite_no"
    sql = sql + " left join s_ts_dict t on h.ftype=cast(t.fdictno as int) and t.fdicttype='3009'"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(h.fcheckdate,'yyyymmdd') between  :ftakedateStart  and :ftakedateEnd "
    sql = sql + " and h.ftype in ('101','104','105','107','108','109','110','111')"
    if cabinet.fsiteNo != "":
        sql = sql + " and h.fsite_no= :fsiteNo "
    if cabinet.fpointNo != "":
        sql = sql + " and i.fpoint_no= :fpointNo "
    if cabinet.boxFequipmentbarcode != "":
        sql = sql + " and h.fboxbarcode= :boxFequipmentbarcode "
    if cabinet.cabFequipmentbarcode != "":
        sql = sql + " and h.fcabbarcode= :cabFequipmentbarcode "
    if cabinet.order == "desc":
        sql = sql + " order by  fupddate desc "
    else:
        sql = sql + " order by fupddate  "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = cabinet.limit
    param["offset"] = cabinet.offset

    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fupddate'] = data[key][0]
        d1['username'] = data[key][1]
        d1['fsiteNo'] = data[key][2]
        d1['fsiteName'] = data[key][3]
        d1['fpointName'] = data[key][4]
        d1['fconname'] = data[key][5]
        d1['fcabname'] = data[key][6]
        d1['fboxname'] = data[key][7]
        d1['faction'] = data[key][8]
        d1['fbiztype'] = data[key][9]
        d1['frfidbarcode'] = data[key][10]
        d1['fgoodname'] = data[key][11]
        d1['fmessage'] = data[key][12]
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": cabinet.limit,
                 "page": cabinet.pageIndex,
                 "pages": (count + cabinet.limit - 1) // cabinet.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)
